﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using System.Collections.Generic;

namespace PhoneNumberOrder
{
    /****************************************************************************************************** 
 * 开发者： 
 * 建立时间： 
 * 进度描述： 
 * 修订描述： 
 * 修改者： 
 * 修改时间： 
 * 详细功能描述：数据库访问操作类，支持事务操作
 * ****************************************************************************************************/
    public class MySqlHelper : IDisposable
    {
        #region 自定义属性
        /// <summary>
        /// 数据库连接属性
        /// </summary>
        public virtual string ConnString
        {
            get
            {

                return "";
            }
        }

        /// <summary>
        /// 连接对象 
        /// </summary>
        protected SqlConnection Conn
        {
            get
            {
                if (m_Conn == null)
                {
                    m_Conn = new SqlConnection(ConnString);
                }
                return m_Conn;
            }
        }
        #endregion

        private SqlConnection m_Conn;
        private SqlTransaction m_Tran;
        private SqlDataAdapter m_Ada;

        #region 构造函数
        public MySqlHelper()
        {
            m_Conn = new SqlConnection(ConnString);
            m_Ada = new SqlDataAdapter();
        }

        public MySqlHelper(String ConnStr)
        {
            m_Conn = new SqlConnection(ConnStr);
            m_Ada = new SqlDataAdapter();
        }
        #endregion

        /// <summary>
        /// 开始事务操作
        /// </summary>
        public void BeginTransaction()
        {
            if (m_Tran == null)
            {
                if (this.Conn.State != ConnectionState.Open)
                    this.Conn.Open();

                m_Tran = this.Conn.BeginTransaction();
            }
        }


        /// <summary>
        /// 结束事务操作
        /// </summary>
        /// <param name="commit">True:提交事务操作，False:回滚事备好操作</param>
        public void EndTransaction(bool commit)
        {
            if (m_Tran != null)
            {
                if (commit)
                    m_Tran.Commit();
                else
                    m_Tran.Rollback();
                m_Tran = null;

            }
        }

        public string QueryObject(string strSQL, List<SqlParameter> paramList)
        {
            SqlCommand cmd = new SqlCommand(strSQL, this.Conn);
            if (this.m_Tran != null)
            {
                cmd.Transaction = this.m_Tran;
            }

            if (paramList != null && paramList.Count > 0)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paramList.ToArray());
            }

            //SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            m_Ada.SelectCommand = cmd;
            DataTable dt = new DataTable();
            m_Ada.Fill(dt);
            string result = "";
            if (dt != null && dt.Rows.Count > 0)
                result = dt.Rows[0][0].ToString();
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        public void FillDataTable(string strSQL, List<SqlParameter> paramList, DataTable dt)
        {
            SqlCommand cmd = new SqlCommand(strSQL, this.Conn);
            if (this.m_Tran != null)
            {
                cmd.Transaction = this.m_Tran;
            }

            if (paramList != null && paramList.Count > 0)
            {
                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(paramList.ToArray());
            }

            //SqlDataAdapter adapter = new SqlDataAdapter(cmd);
            m_Ada.SelectCommand = cmd;
            m_Ada.Fill(dt);
        }


        

        public int ExecuteNonQuery(string strSQL, List<SqlParameter> paramList)
        {
            SqlCommand cmd = new SqlCommand(strSQL, this.Conn);
            if (this.m_Tran != null)
            {
                cmd.Transaction = this.m_Tran;
            }

            if (paramList != null && paramList.Count > 0)
            {
                cmd.Parameters.AddRange(paramList.ToArray());
            }

            try
            {
                if (this.Conn.State == ConnectionState.Closed)
                    this.Conn.Open();

                return cmd.ExecuteNonQuery();
            }
            finally
            {
                //关闭没有设置事务的已经打开的连接（结束事务时会关闭连接）
                if (this.Conn.State != ConnectionState.Closed && this.m_Tran == null)
                    cmd.Connection.Close();
            }
        }

        /// <summary>
        /// 更新来自FillDataset方法填充的数据集
        /// </summary>
        /// <param name="ds">要同步的数据库的数据集</param>
        /// <returns>已更新到数据库的行数</returns>
        public int UpdateDataset(DataSet ds)
        {
            if (m_Ada == null || m_Ada.SelectCommand == null || m_Ada.SelectCommand.CommandText == string.Empty)
            {
                return 0;
            }

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_Ada);
            m_Ada.InsertCommand = cmdBuilder.GetInsertCommand();
            m_Ada.UpdateCommand = cmdBuilder.GetUpdateCommand();
            m_Ada.DeleteCommand = cmdBuilder.GetDeleteCommand();

            m_Ada.AcceptChangesDuringUpdate = true;
            return m_Ada.Update(ds);
        }
        public int UpdateDataset(DataSet ds,string tableName)
        {
            if (m_Ada == null || m_Ada.SelectCommand == null || m_Ada.SelectCommand.CommandText == string.Empty)
            {
                return 0;
            }

            SqlCommandBuilder cmdBuilder = new SqlCommandBuilder(m_Ada);
            m_Ada.InsertCommand = cmdBuilder.GetInsertCommand();
            m_Ada.UpdateCommand = cmdBuilder.GetUpdateCommand();
            m_Ada.DeleteCommand = cmdBuilder.GetDeleteCommand();

            m_Ada.AcceptChangesDuringUpdate = true;
            return m_Ada.Update(ds, tableName);
        }


        #region IDisposable 成员

        public void Dispose()
        {
            if (m_Ada != null)
            {
                m_Ada.Dispose();
                m_Ada = null;
            }
            if (m_Tran != null)
            {
                m_Tran.Rollback();
                m_Tran = null;
            }

            if (m_Conn != null)
            {
                if (m_Conn.State != ConnectionState.Closed)
                {
                    m_Conn.Close();
                    m_Conn = null;
                }
            }
        }

        #endregion
    }
}
